package com.rdc.example.dao.impl;


import com.rdc.example.entity.Column;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import static com.rdc.example.util.DBUtil.closeResources;
import static com.rdc.example.util.DBUtil.getConnection;

/**
 * @author duck
 */
public class ColumnDaoImpl implements com.rdc.example.dao.ColumnDao {


    /**
     *
     * @param column
     * @return
     * @throws Exception
     */
    @Override
    public boolean insert(Column column) throws Exception {
        // 进行数据库插入操作
        Connection conn =null;
        PreparedStatement pstmt=null;
        try {
            conn = getConnection();
            String sql ="INSERT INTO `column` (user_id ,name) VALUES (?,?)";
            pstmt = conn.prepareStatement(sql);
            insertColumn(pstmt, column);
            int row = pstmt.executeUpdate();
            return row > 0;
        }catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            closeResources(null, pstmt, conn);
        }
    }

    /**
     *
     * @param id
     * @return
     * @throws Exception
     */
    @Override
    public boolean deleteById(Integer id) throws Exception {
        // 进行数据库删除操作
        Connection conn =null;
        PreparedStatement pstmt=null;
        //ResultSet rs=null;
        try {
            conn = getConnection();
            String sql = "delete from `column` where id=?" ;
            pstmt = conn.prepareStatement(sql);
            pstmt.setLong(1, id);
            int row = pstmt.executeUpdate();
            return row > 0;
        } catch (SQLIntegrityConstraintViolationException e) {
            e.printStackTrace();
        }finally {
            closeResources(null,pstmt,conn);
        }
        return false;
    }

    /**
     *
     * @param column
     * @return
     * @throws Exception
     */
    @Override
    public boolean update(Column column) throws Exception {
        // 进行数据库更新操作
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = getConnection();
            String sql = "update `column` set user_id =?,name=? where id=?";
            pstmt = conn.prepareStatement(sql);
            insertColumn(pstmt, column);
            pstmt.setInt(3, column.getId());
            int row = pstmt.executeUpdate();
            return row > 0;
        }
//        catch (SQLException e) {                       // 1. 捕获具体异常
//            System.err.println("数据库更新失败: "+ e);
//            return false;
//        }
        catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            closeResources(null, pstmt, conn);
        }
    }

    /**
     *
     * @param userId
     * @return
     * @throws Exception
     */
    @Override
    public List<Column> listColumnByUserId(Integer userId) throws Exception {
        // 进行数据库查询操作
        Connection conn = getConnection();
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        List<Column> columnList = new ArrayList<>();
        Column column;
        try {
            String sql = "SELECT * from `column` where user_id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                column=getColumn(rs);
                columnList.add(column);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            closeResources(rs, pstmt, conn);
        }
        return columnList;
    }

    /**
     *
     * @param keyword
     * @return
     * @throws Exception
     */
    @Override
    public List<Column> searchColumnsByName(String keyword) throws Exception {
        List<Column> columnList = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            String sql = "select * from `column` where name like ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "%" + keyword + "%");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                columnList.add(getColumn(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResources(rs, pstmt, conn);
        }
        return columnList;
    }

    private Column getColumn(ResultSet rs) throws SQLException {
        Column column = new Column();
        column.setId(rs.getInt("id"));
        column.setName(rs.getString("name"));
        column.setUserId(rs.getInt("user_id"));
        column.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
        return column;
    }

    private void insertColumn(PreparedStatement pstmt, Column column) throws SQLException {
        pstmt.setInt(1, column.getUserId());
        pstmt.setString(2,column.getName());

    }



}
